
-- =============================================
-- Author:      Jónheiður Ísleifsdóttir
-- Create Date: 20.11.2020
-- Description: This procedure creates and updates the fItemCostAdjustment table used to update fInventory table
-- and to update adjusted cost.
-- =============================================
ALTER PROCEDURE [dbo].[prefactItemCostAdjustment] 
    
AS
BEGIN

/*TRUNCATE table so cost can be recalculated*/
TRUNCATE TABLE [DW].[fItemCostAdjustment];

WITH 
tItemValueEntry
AS
(SELECT
		sVAE.[CompanyPrefix]
	   ,sVAE.[Item No_]
	   ,sVAE.[Posting Date]
	   ,sVAE.[Item Ledger Entry Type]
	   ,sVAE.[Entry Type]
	   ,sVAE.[Variance Type]
	   ,sVAE.[Item Charge No_]
	   ,sVAE.[Location Code]
	   ,sVAE.[Variant Code]
	   ,COUNT_BIG(*) AS [$Cnt]
	   ,CAST(SUM(sVAE.[Invoiced Quantity]) AS NUMERIC(28, 10)) AS [SUM$Invoiced Quantity]
	   ,CAST(SUM(sVAE.[Sales Amount (Expected)]) AS NUMERIC(28, 10)) AS [SUM$Sales Amount (Expected)]
	   ,CAST(SUM(sVAE.[Sales Amount (Actual)]) AS NUMERIC(28, 10)) AS [SUM$Sales Amount (Actual)]
	   ,CAST(SUM(sVAE.[Cost Amount (Expected)]) AS NUMERIC(28, 10)) AS [SUM$Cost Amount (Expected)]
	   ,CAST(SUM(sVAE.[Cost Amount (Actual)]) AS NUMERIC(28, 10)) AS [SUM$Cost Amount (Actual)]
	   ,CAST(SUM(sVAE.[Cost Amount (Non-Invtbl_)]) AS NUMERIC(28, 10)) AS [SUM$Cost Amount (Non-Invtbl_)]
	   ,CAST(SUM(sVAE.[Purchase Amount (Actual)]) AS NUMERIC(28, 10)) AS [SUM$Purchase Amount (Actual)]
	   ,CAST(SUM(sVAE.[Expected Cost Posted to G_L]) AS NUMERIC(28, 10)) AS [SUM$Expected Cost Posted to G_L]
	   ,CAST(SUM(sVAE.[Cost Posted to G_L]) AS NUMERIC(28, 10)) AS [SUM$Cost Posted to G_L]
	   ,CAST(SUM(sVAE.[Item Ledger Entry Quantity]) AS NUMERIC(28, 10)) AS [SUM$Item Ledger Entry Quantity]
	   ,sVAE.[bigint_timestamp] AS [RowID]
	FROM [stg$Value Entry] sVAE
	GROUP BY sVAE.[CompanyPrefix]
			,sVAE.[Item No_]
			,sVAE.[Posting Date]
			,sVAE.[Item Ledger Entry Type]
			,sVAE.[Entry Type]
			,sVAE.[Variance Type]
			,sVAE.[Item Charge No_]
			,sVAE.[Location Code]
			,sVAE.[Variant Code]
			,sVAE.[bigint_timestamp]),

tValueEntry
AS
(SELECT
		tIVE.[CompanyPrefix]
	   ,tIVE.[Item No_]
	   ,tIVE.[Variant Code]
	   ,tIVE.[Location Code]
	   ,tIVE.[Posting Date] AS [ValidFromDate]
	   ,IIF(SUM(tIVE.[SUM$Cost Amount (Actual)]) = 0 AND SUM(tIVE.[SUM$Invoiced Quantity]) = 0
		, SUM(tIVE.[SUM$Cost Amount (Expected)]) / NULLIF(SUM(tIVE.[SUM$Item Ledger Entry Quantity]), 0)
		, SUM(tIVE.[SUM$Cost Amount (Actual)]) / NULLIF(SUM(tIVE.[SUM$Invoiced Quantity]), 0)) AS [WeightedItemAverageCost]
	FROM [tItemValueEntry] tIVE
	WHERE tIVE.[Item Ledger Entry Type] = 1
	GROUP BY tIVE.[CompanyPrefix]
			,tIVE.[Item No_]
			,tIVE.[Posting Date]
			,tIVE.[Variant Code]
			,tIVE.[Location Code]),

tItemCostAdjustment
AS
(SELECT
		tVAE.[CompanyPrefix]
	   ,tVAE.[Item No_]
	   ,tVAE.[Variant Code]
	   ,tVAE.[Location Code]
	   ,MIN(tVAE.[ValidFromDate]) AS [ValidFromDate]
	   ,tVAE.[WeightedItemAverageCost]
	FROM [tValueEntry] tVAE
	GROUP BY tVAE.[CompanyPrefix]
			,tVAE.[Item No_]
			,tVAE.[Variant Code]
			,tVAE.[Location Code]
			,tVAE.[WeightedItemAverageCost])

INSERT INTO [DW].[fItemCostAdjustment] WITH (TABLOCK) 
( [Company]
, [Item No_]
, [Variant Code]
, [Location Code]
, [ValidFromDate]
, [ValidToDate]
, [WeightedItemAverageCost]
, [Batchdate]
)

	SELECT
		dCOM.[SK_Company] AS [Company]
	   ,tICA.[Item No_]
	   ,tICA.[Variant Code]
	   ,tICA.[Location Code]
	   ,IIF(LEAD(MIN(tICA.[ValidFromDate])) OVER (PARTITION BY tICA.[CompanyPrefix], tICA.[Item No_], tICA.[Variant Code], tICA.[Location Code] ORDER BY tICA.ValidFromDate DESC) IS NULL, CAST('1901-01-01' AS DATE), MIN(tICA.ValidFromDate)) AS [ValidFromDate]
	   ,ISNULL(DATEADD(DAY, -1, LAG(MIN(tICA.[ValidFromDate])) OVER (PARTITION BY tICA.[CompanyPrefix], tICA.[Item No_], tICA.[Variant Code], tICA.[Location Code] ORDER BY tICA.[ValidFromDate] DESC)), CAST(GETUTCDATE() AS DATE)) AS [ValidToDate]
	   ,tICA.[WeightedItemAverageCost] AS [WeightedItemAverageCost]
	   ,GETUTCDATE() AS [Batchdate]
	FROM tItemCostAdjustment tICA
	LEFT JOIN [DW].[dCompany] dCOM
		ON tICA.[CompanyPrefix] = dCOM.[CompanyPrefix]
	WHERE tICA.[WeightedItemAverageCost] IS NOT NULL
	GROUP BY tICA.[CompanyPrefix]
			,dCOM.[SK_Company]
			,tICA.[Item No_]
			,tICA.[Variant Code]
			,tICA.[Location Code]
			,tICA.[ValidFromDate]
			,tICA.[WeightedItemAverageCost]
;
END

SELECT
	'RowCount' = @@rowcount
	
	
GO
-- =============================================
-- Modified date: 2021-04-14 Use Merge to eliminate risk of duplicate rows
-- Description:	SP with option of Incremental load for [fHotelDetailedRevenue]
-- =============================================

ALTER PROCEDURE [dbo].[factHotelDetailedRevenue] 

	@HDREntryNo bigint,
	@HDRNewEntryNo bigint 
	 	
AS
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Stg$Detailed Revenue Entry')
BEGIN

SET NOCOUNT ON;

WITH

/*
Get the list of companies used
*/
tCompanies
AS
(SELECT
		dCOM.[SK_Company] as [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1),

/*Get the dimensions*/
tLocation
AS
(SELECT
		dLOC.[SK_location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),

tItem
AS
(SELECT
		dITM.[SK_Item]
	   ,tCOM.[CompanyPrefix]
	   ,dITM.[No]
	FROM [DW].[dItem] dITM
	LEFT OUTER JOIN tCompanies tCOM
		ON dITM.[Company] = tCOM.[Company]),

tPOSTerminal
AS
(SELECT
		tCOM.[CompanyPrefix]
	   ,dPOT.[SK_POSTerminal]
	   ,dPOT.[No]
	FROM [DW].[dPOSTerminal] dPOT
	LEFT OUTER JOIN tCompanies tCOM
		ON dPOT.[Company] = tCOM.[Company]),

tHotelRooms
AS
(SELECT
		tCOM.[CompanyPrefix]
	   ,dHOR.[SK_HotelRooms]
	   ,dHOR.[Room]
	FROM [DW].[dHotelRooms] dHOR
	LEFT OUTER JOIN tCompanies tCOM
		ON dHOR.[Company] = tCOM.[Company]),

tHotelReservations
AS
(SELECT
		tCOM.[CompanyPrefix]
	   ,dHRE.[SK_HotelReservations]
	   ,dHRE.[Reservation No_]
	FROM [DW].[dHotelReservations] dHRE
	LEFT OUTER JOIN tCompanies tCOM
		ON dHRE.[Company] = tCOM.[Company]),

tHotelDetailedRevenue AS(
SELECT
	COALESCE(dCOM.[SK_Company],-1) AS [Company]
   ,sDRE.[Entry No_]
   ,sDRE.[Date]
   ,COALESCE(tHRE.SK_HotelReservations, -1) AS [SK_HotelReservations]
   ,COALESCE(tHRO.SK_HotelRooms, -1) AS [SK_HotelRooms]
   ,COALESCE(tITM.SK_Item, -1) AS [SK_Item]
   ,COALESCE(tLOC.SK_Location, -1) AS [SK_Location]
   ,COALESCE(tPOT.SK_POSTerminal, -1) AS [SK_POSTerminal]
   ,sDRE.[Quantity]
   ,sDRE.[Unit Price]
   ,sDRE.[Line Discount _]
   ,sDRE.[Line Discount Amount]
   ,sDRE.[Amount (LCY)]
   ,sDRE.[VAT Amount (LCY)]
FROM [Stg$Detailed Revenue Entry] sDRE
LEFT JOIN [tHotelRooms] tHRO
	ON sDRE.[CompanyPrefix] = tHRO.[CompanyPrefix]
		AND sDRE.[Room No_] = tHRO.[Room]
LEFT JOIN [tHotelReservations] tHRE
	ON sDRE.[CompanyPrefix] = tHRE.[CompanyPrefix]
		AND sDRE.[Reservation No_] = tHRE.[Reservation No_]
LEFT JOIN [tItem] tITM
	ON sDRE.[CompanyPrefix] = tITM.[CompanyPrefix]
		AND sDRE.[Item No_] = tITM.[No]
LEFT JOIN [tLocation] tLOC
	ON sDRE.[CompanyPrefix] = tLOC.[CompanyPrefix]
		AND sDRE.[Store No_] = tLOC.[LocationCode]
LEFT JOIN [tPOSTerminal] tPOT
	ON sDRE.[CompanyPrefix] = tPOT.[CompanyPrefix]
		AND sDRE.[POS Terminal No_] = tPOT.[No]
LEFT JOIN [DW].[dCompany] dCOM
	ON dCOM.[CompanyPrefix] = sDRE.[CompanyPrefix]
WHERE sDRE.[Entry No_] > @HDREntryNo
	AND sDRE.[Entry No_] <= @HDRNewEntryNo)

MERGE [DW].[fHotelDetailedRevenue] AS Target USING 
	(SELECT 
	[Company]
	,[Entry No_]
	,[Date]
	,[SK_HotelReservations]
	,[SK_HotelRooms]
	,[SK_Item]
	,[SK_Location]
	,[SK_POSTerminal]
	,[Quantity]
	,[Unit Price]
	,[Line Discount _]
	,[Line Discount Amount]
	,[Amount (LCY)]
	,[VAT Amount (LCY)]
	FROM tHotelDetailedRevenue) AS Source
	ON
	Target.[Company] = Source.[Company]
	AND Target.[Entry No_] = Source.[Entry No_]
	AND Target.[Date] = Source.[Date]

	WHEN MATCHED
	THEN UPDATE
		SET [SK_HotelReservations] = Source.[SK_HotelReservations]
		,[SK_HotelRooms] = Source.[SK_HotelRooms]
		,[SK_Item] = Source.[SK_Item]
		,[SK_Location] = Source.[SK_Location]
		,[SK_POSTerminal] = Source.[SK_POSTerminal]
		,[Quantity] = Source.[Quantity]
		,[Unit Price] = Source.[Unit Price]
		,[Line Discount _] = Source.[Line Discount _]
		,[Line Discount Amount] = Source.[Line Discount Amount]
		,[Amount (LCY)] = Source.[Amount (LCY)]
		,[VAT Amount (LCY)] = Source.[VAT Amount (LCY)]

		WHEN NOT MATCHED BY TARGET
	THEN INSERT
	([Company]
           ,[Entry No_]
           ,[Date]
           ,[SK_HotelReservations]
           ,[SK_HotelRooms]
           ,[SK_Item]
           ,[SK_Location]
           ,[SK_POSTerminal]
           ,[Quantity]
           ,[Unit Price]
           ,[Line Discount _]
           ,[Line Discount Amount]
           ,[Amount (LCY)]
           ,[VAT Amount (LCY)])
     VALUES
           (Source.[Company]
           ,Source.[Entry No_]
           ,Source.[Date]
           ,Source.[SK_HotelReservations]
           ,Source.[SK_HotelRooms]
           ,Source.[SK_Item]
           ,Source.[SK_Location]
           ,Source.[SK_POSTerminal]
           ,Source.[Quantity]
           ,Source.[Unit Price]
           ,Source.[Line Discount _]
           ,Source.[Line Discount Amount]
           ,Source.[Amount (LCY)]
           ,Source.[VAT Amount (LCY)]);


SELECT
	'RowCount' = @@rowcount
;

END
GO